"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 基础功能
Case Name   : 频繁使gpc失效
Create At   : 2021/05/31
Owner       : opentestcase026
Description :
    1.开启GPC
    2.重启数据库
    3.创建表
    4.创建prepare语句
    5.调用p1_1
    6.查询计划
    7.alter patririon
    8.查询计划
    9.重复执行20次
Expect      :
    1.开启GPC成功
    2.重启数据库成功
    3.创建表成功
    4.成功
    5.成功
    6.有计划
    7.成功
    8.无计划
    9.重复执行成功
History     :
    Modified by opentestcase033 2023/8/7: 步骤9重复执行1000次改为20次，
    teardown中打印执行结果并添加断言
"""
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Gpcclass(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info("-----------this is setup-----------")
        self.log.info("Opengauss_Function_GPC_case0008 start")
        self.constant = Constant()
        self.commonshpri = CommonSH('PrimaryDbUser')
        self.tb_name = "gpc_tb"
        self.pre_name = "gpc_tb_pre"

        result = self.commonshpri.execut_db_sql('show enable_thread_pool;')
        self.log.info(f"enable_thread_pool is {result}")
        self.enable_thread_pool = result.strip().splitlines()[-2]

        result = self.commonshpri.execut_db_sql(
            'show enable_global_plancache;')
        self.log.info(f"enable_global_plancache is {result}")
        self.enable_global_plancache = result.strip().splitlines()[-2]

    def test_index(self):
        self.log.info('-------------1.开启GPC-----------')
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG, "enable_thread_pool = on")
        self.assertTrue(result)
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            "enable_global_plancache = on")
        self.assertTrue(result)

        self.log.info('--------------2.重启数据库------------------')
        result = self.commonshpri.restart_db_cluster()
        self.assertTrue(result)

        self.log.info("---------------3.创建表----------------------")
        cmd = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(i int) " \
            f"partition by range(i) " \
            f"(partition p1 values less than (100)," \
            f"partition p2 values less than (1000));"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result)

        for i in range(1, 21):
            self.log.info(f"======round {i}=========")
            self.log.info("----------4.创建prepare语句5.执行prepare----------")
            cmd = f"prepare {self.pre_name} as " \
                f"select * from {self.tb_name};" \
                f"execute {self.pre_name};"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.PREPARE_SUCCESS_MSG, result)
            self.assertIn('0 rows', result)

            self.log.info("-----------6.查询计划:有------------------")
            cmd = f"select * from dbe_perf.global_plancache_status " \
                f"where query='prepare {self.pre_name} " \
                f"as select * from {self.tb_name};' " \
                f"and valid = 't';"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn('(1 row)', result)

            self.log.info("-----------7.alter------------------")
            cmd = f"ALTER TABLE {self.tb_name} DROP PARTITION p2;"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.ALTER_TABLE_MSG, result)

            self.log.info("-----------8.查询计划:无------------------")
            cmd = f"select * from dbe_perf.global_plancache_status "
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn('(0 rows)', result)

            self.log.info("-----------9.alter------------------")
            cmd = f"ALTER TABLE {self.tb_name} " \
                f"add PARTITION p2 values less than (1000);"
            result = self.commonshpri.execut_db_sql(cmd)
            self.log.info(result)
            self.assertIn(self.constant.ALTER_TABLE_MSG, result)

    def tearDown(self):
        self.log.info('------------this is tearDown-------------')
        self.log.info('------------------清理环境-------------')
        cmd = f"drop table if exists {self.tb_name} cascade;"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        res1 = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_thread_pool = {self.enable_thread_pool}")
        self.log.info(res1)
        res2 = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_global_plancache = {self.enable_global_plancache}")
        self.log.info(res2)
        restart_res = self.commonshpri.restart_db_cluster()
        self.log.info(restart_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result, f"删除表失败")
        self.assertTrue(res1, f"恢复参数enable_thread_pool失败")
        self.assertTrue(res2, f"恢复参数enable_global_plancache失败")
        self.assertTrue(restart_res, f"重启数据库失败")
        self.log.info("-Opengauss_Function_GPC_case0008 end-")
